﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_update_server]
#-- Purpose:		Updates the server discovery record with the latest 
#--					information
#--	Last Update:	05/16/2013
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_update_server]
(
	@server_name		varchar(60),
	@instance_name		varchar(60)		= NULL,
	@sql_service		varchar(255)	= NULL,
	@agent_service		varchar(255)	= NULL,
	@sql_start_mode		varchar(10)		= NULL,
	@sql_state			varchar(20)		= NULL,
	@agent_start_mode	varchar(10)		= NULL,
	@agent_state		varchar(20)		= NULL,
	@engine_edition		int				= NULL,
	@edition_id			int				= NULL,
	@product_version	varchar(25)		= NULL,
	@product_level		varchar(5)		= NULL,
	@has_master_jobs	bit				= 0,
	@has_local_jobs		bit				= 0,
	@backup_directory	varchar(255)	= NULL,
	@is_mixed_mode		bit				= 0,
	@has_centraldba_db	bit				= 0,
	@pwrshl_script_dir  varchar(2000)   = NULL,
	@is_error			bit				= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @id INT

--- Parameter Preprocessing
IF LTRIM(RTRIM(@instance_name)) = ''
	SET @instance_name = NULL
IF LTRIM(RTRIM(@sql_service)) = ''
	SET @sql_service = NULL
IF LTRIM(RTRIM(@agent_service)) = ''
	SET @agent_service = NULL
IF LTRIM(RTRIM(@sql_start_mode)) = ''
	SET @sql_start_mode = NULL
IF LTRIM(RTRIM(@sql_state)) = ''
	SET @sql_state = NULL
IF LTRIM(RTRIM(@agent_start_mode)) = ''
	SET @agent_start_mode = NULL
IF LTRIM(RTRIM(@agent_state)) = ''
	SET @agent_state = NULL
IF LTRIM(RTRIM(@pwrshl_script_dir)) = ''
	SET @pwrshl_script_dir = NULL

--- Failsafe; in case the SQL Server name is passed in instead of the physical server name
IF @server_name LIKE '%\%'
	SELECT @server_name = SUBSTRING(@server_name, 0, CHARINDEX(@server_name, '\') - 1)

--- Get the Server ID
SELECT		@id = ssd_id
FROM		[dbo].[tblMSX_server_discovery] (NOLOCK)
WHERE		ssd_server_name = @server_name
			AND ISNULL(ssd_instance_name, '') = ISNULL(@instance_name, '')

--- Was this server previously discovered?
IF @id IS NOT NULL
  BEGIN
	--- Mark that there was an error reading the server
	IF @is_error = 1
	  BEGIN
		UPDATE		[dbo].[tblMSX_server_discovery]
		SET			ssd_last_update = GETDATE(),
					ssd_last_error = GETDATE()
		WHERE		ssd_id = @id
	  END
	
	ELSE
	  BEGIN
		--- Server Record data has changed; update the data
		IF EXISTS	(
					SELECT		TOP 1 * 
					FROM		[dbo].[tblMSX_server_discovery] (NOLOCK)
					WHERE		ssd_id = @id
								AND (
									ISNULL(ssd_sql_service, '') <> ISNULL(@sql_service, '')
									OR ISNULL(ssd_agent_service, '') <> ISNULL(@agent_service, '')
									OR ISNULL(ssd_sql_start_mode, '') <> ISNULL(@sql_start_mode, '')
									OR ISNULL(ssd_sql_state, '') <> ISNULL(@sql_state, '')
									OR ISNULL(ssd_agent_start_mode, '') <> ISNULL(@agent_start_mode, '')
									OR ISNULL(ssd_agent_state, '') <> ISNULL(@agent_state, '')
									OR ISNULL(ssd_pwrshl_script_dir, '') <> ISNULL(@pwrshl_script_dir, '')
								)
					)
		  BEGIN
			UPDATE		[dbo].[tblMSX_server_discovery]
			SET			ssd_sql_service = @sql_service,
						ssd_agent_service = @agent_service,
						ssd_sql_start_mode = @sql_start_mode,
						ssd_sql_state = @sql_state,
						ssd_agent_start_mode = @agent_start_mode,
						ssd_agent_state = @agent_state,
						ssd_pwrshl_script_dir = @pwrshl_script_dir,
						ssd_last_update = GETDATE()
			WHERE		ssd_id = @id
						AND (
							ISNULL(ssd_sql_service, '') <> ISNULL(@sql_service, '')
							OR ISNULL(ssd_agent_service, '') <> ISNULL(@agent_service, '')
							OR ISNULL(ssd_sql_start_mode, '') <> ISNULL(@sql_start_mode, '')
							OR ISNULL(ssd_sql_state, '') <> ISNULL(@sql_state, '')
							OR ISNULL(ssd_agent_start_mode, '') <> ISNULL(@agent_start_mode, '')
							OR ISNULL(ssd_agent_state, '') <> ISNULL(@agent_state, '')
							OR ISNULL(ssd_pwrshl_script_dir, '') <> ISNULL(@pwrshl_script_dir, '')
						)
		  END

		--- Just mark that the Server was discovered still
		ELSE  
		  BEGIN
			UPDATE		[dbo].[tblMSX_server_discovery]
			SET			ssd_last_update = GETDATE()
			WHERE		ssd_id = @id
		  END
	  END
  END

--- Insert New Server Discovery Record
ELSE 
  BEGIN
	--- Insert the Server specific information
	INSERT INTO [dbo].[tblMSX_server_discovery]
		(
			ssd_last_error,
			ssd_last_update,
			ssd_date_discovered,
			ssd_server_name,
			ssd_instance_name,
			ssd_sql_service,
			ssd_agent_service,
			ssd_sql_start_mode,
			ssd_sql_state,
			ssd_agent_start_mode,
			ssd_agent_state,
			ssd_pwrshl_script_dir
		)
	VALUES
		(
			(CASE WHEN @is_error = 1 THEN GETDATE() ELSE NULL END),
			GETDATE(),
			GETDATE(),
			@server_name,
			@instance_name,
			@sql_service,
			@agent_service,
			@sql_start_mode,
			@sql_state,
			@agent_start_mode,
			@agent_state,
			@pwrshl_script_dir
		)

	--- Retrieve the ID of the new record
	SET @id = SCOPE_IDENTITY()
  END

--- Update SQL Server Specific Information
EXEC [dbo].[spMSX_update_sqlserver]		@server_name = @server_name,
										@instance_name = @instance_name,
										@engine_edition = @engine_edition,
										@edition_id = @edition_id,
										@product_version = @product_version,
										@product_level = @product_level,
										@has_master_jobs = @has_master_jobs,
										@has_local_jobs = @has_local_jobs,
										@backup_directory = @backup_directory,
										@is_mixed_mode = @is_mixed_mode,
										@has_centraldba_db = @has_centraldba_db

SET NOCOUNT OFF